{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "646516c0",
   "metadata": {},
   "source": [
    "Time Series of US Deposit and Swap Rates\n",
    "========================================\n",
    "\n",
    "This notebook demonstrates how to download time series of USD deposit and swap rates from the US Federal Reserve Board web site. \n",
    "\n",
    "The data is obtained from the site www.federalreserve.gov. The time series can be downloaded from a web browser, but they can also be downloaded programmatically. The site provides directions on how to construct the URL corresponding to each particular data set. In the example below, the URL is specific to the H15 table, with all available deposit and swap rates included.\n",
    "\n",
    "In this notebook, we download 11 years of daily data, from January 2000 to December 2011. The data is stored in a pandas DataFrame for further processing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2992776a",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "import os, urllib, datetime, pandas\n",
    "import numpy as np\n",
    "import math\n",
    "\n",
    "from pandas.io.parsers import read_csv\n",
    "from datetime import date"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8235e1df",
   "metadata": {},
   "source": [
    "## Utility functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e3c99751",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "def get_frb_url(dtStart, dtEnd):\n",
    "    \"\"\"\n",
    "    Federal Reserve Board URL\n",
    "    Construct this URL at 'http://www.federalreserve.gov/datadownload\n",
    "    \"\"\"\n",
    "    \n",
    "    url = 'http://www.federalreserve.gov/datadownload/Output.aspx?rel=H15&series=8f47c9df920bbb475f402efa44f35c29&lastObs=&from=%s&to=%s&filetype=csv&label=include&layout=seriescolumn' % (dtStart.strftime('%m/%d/%Y'), dtEnd.strftime('%m/%d/%Y'))\n",
    "    return url\n",
    "\n",
    "def dataconverter(s):\n",
    "    \"\"\"\n",
    "    The FRB data file has \n",
    "    - numeric cells\n",
    "    - empty cells\n",
    "    - cells with 'NC' or 'ND'\n",
    "    \"\"\"\n",
    "    try:\n",
    "        res = float(s)\n",
    "    except:\n",
    "        res = np.nan\n",
    "    return res\n",
    "\n",
    "def good_row(z):\n",
    "    \"\"\"\n",
    "    Retain days with no gaps (0 or NaN) in data\n",
    "    \"\"\"\n",
    "\n",
    "    try:\n",
    "        res = not ((z.isnull()) | (z == 0)).any()\n",
    "    except:\n",
    "        res = False\n",
    "    return res"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e30a142e",
   "metadata": {},
   "source": [
    "## Download or read the data, convert to data frame and save  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b0b0f6d5",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             Swap1Y  Swap2Y  Swap3Y  Swap4Y  Swap5Y  Swap7Y  Swap10Y  Swap30Y  \\\n",
      "Time Period                                                                     \n",
      "2000-07-03     7.10    7.16    7.17    7.17    7.17    7.20     7.24     7.24   \n",
      "2000-07-05     7.03    7.06    7.07    7.07    7.08    7.11     7.14     7.16   \n",
      "2000-07-06     7.07    7.13    7.14    7.15    7.16    7.19     7.21     7.21   \n",
      "2000-07-07     7.01    7.04    7.06    7.06    7.07    7.10     7.14     7.14   \n",
      "2000-07-10     7.04    7.09    7.11    7.13    7.14    7.17     7.20     7.19   \n",
      "...             ...     ...     ...     ...     ...     ...      ...      ...   \n",
      "2011-12-14     0.70    0.72    0.82    1.02    1.26    1.68     2.08     2.66   \n",
      "2011-12-15     0.68    0.72    0.82    1.03    1.25    1.67     2.06     2.62   \n",
      "2011-12-16     0.68    0.70    0.79    1.00    1.23    1.64     2.04     2.61   \n",
      "2011-12-19     0.70    0.74    0.83    1.02    1.25    1.64     2.02     2.58   \n",
      "2011-12-20     0.69    0.74    0.85    1.05    1.27    1.68     2.06     2.61   \n",
      "\n",
      "             Libor1M  Libor3M  Libor6M  \n",
      "Time Period                             \n",
      "2000-07-03      6.56     6.72     6.94  \n",
      "2000-07-05      6.56     6.69     6.84  \n",
      "2000-07-06      6.56     6.69     6.84  \n",
      "2000-07-07      6.53     6.66     6.81  \n",
      "2000-07-10      6.56     6.66     6.81  \n",
      "...              ...      ...      ...  \n",
      "2011-12-14      0.35     0.49     0.71  \n",
      "2011-12-15      0.35     0.49     0.71  \n",
      "2011-12-16      0.35     0.49     0.71  \n",
      "2011-12-19      0.36     0.50     0.72  \n",
      "2011-12-20      0.35     0.50     0.73  \n",
      "\n",
      "[2858 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "fname = os.path.join('..', 'data', 'frb_h15.csv')\n",
    "\n",
    "if not os.path.isfile(fname):\n",
    "    url = get_frb_url(dtStart=date(2000,1,1),\n",
    "                          dtEnd=date(2011,12,20))\n",
    "    frb_site = urllib.urlopen(url)\n",
    "    text = frb_site.read().strip()\n",
    "\n",
    "    f = open(fname, 'w')\n",
    "    f.write(text)\n",
    "    f.close()\n",
    "\n",
    "# simpler labels\n",
    "columns_dic = {\"RIFLDIY01_N.B\":'Swap1Y',\n",
    "               \"RIFLDIY02_N.B\":'Swap2Y',\n",
    "               \"RIFLDIY03_N.B\":'Swap3Y',\n",
    "               \"RIFLDIY04_N.B\":'Swap4Y',\n",
    "               \"RIFLDIY05_N.B\":'Swap5Y',\n",
    "               \"RIFLDIY07_N.B\":'Swap7Y',\n",
    "               \"RIFLDIY10_N.B\":'Swap10Y',\n",
    "               \"RIFLDIY30_N.B\":'Swap30Y',\n",
    "               \"RILSPDEPM01_N.B\":'Libor1M',\n",
    "               \"RILSPDEPM03_N.B\":'Libor3M',\n",
    "               \"RILSPDEPM06_N.B\":'Libor6M'}\n",
    "\n",
    "# the data converter is applied to all columns\n",
    "# excluding the index column (0)\n",
    "\n",
    "dc_dict = {i: dataconverter for i\n",
    "        in range(1,len(columns_dic) +1)}\n",
    "\n",
    "# read the csv file and convert to a DataFrame\n",
    "\n",
    "df_libor = read_csv(fname, sep=',', header=0,\n",
    "                    index_col=0, parse_dates=True,\n",
    "                    converters=dc_dict,\n",
    "                    skiprows=[0,1,2,3,4])\n",
    "\n",
    "# change column names, remove rows with NaN and save data frame\n",
    "    \n",
    "df_libor = df_libor.rename(columns=columns_dic)\n",
    "good_rows = df_libor.apply(good_row, axis=1)\n",
    "df_libor_good = df_libor[good_rows]\n",
    "print(df_libor_good)\n",
    "\n",
    "df_libor_good.to_pickle(os.path.join('..', 'data', 'df_libor.pkl'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff71a5ca-924d-4ca4-8dd6-720066c1ce06",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
